var crypto = require('crypto');

module.exports = {
    getByUser:'SELECT * FROM user_info WHERE USER_NAME=? AND PASSWORD=?',
    getUserRole:'SELECT* FROM user_role WHERE ID=?',
    getUserCount:'SELECT count(*) AS COUNT FROM user_info',
    removeUser:'DELETE FROM user_info WHERE ID = ?',
    getUserAll:function(params){
    	var sqlStr = 'SELECT * FROM user_info ORDER BY REGISTRY_TIME DESC LIMIT '+params.start+','+params.end+'';
    	if(params.keyWord.length > 0){
    		sqlStr = 'SELECT * FROM user_info WHERE USER_NAME LIKE "%'+params.keyWord+'%" OR REAL_NAME LIKE "%'+params.keyWord+'%" OR CELL_PHONE LIKE "%'+params.keyWord+'%" ORDER BY REGISTRY_TIME DESC LIMIT '+params.start+','+params.end+'';
    	}
    	return sqlStr;
    },
    addUser:function(param){
        var valueArr=[] , nameArr=[];
        for(var i in param){
            if(i == 'PASSWORD'){
                var md5 = crypto.createHash('md5');
                md5.update(param[i],'utf-8');
                var val = md5.digest('hex');
                param[i] = val.toUpperCase();
            }
            if(i != 'ID'){
                valueArr.push("'"+param[i]+"'");
                nameArr.push(i);
            }
        }
        return 'INSERT INTO user_info('+nameArr.join(',')+') VALUES('+valueArr.join(',')+')';
    },
    updateUser:function(param){
    	var setNameArr=[];
		for(var i in param){
            if(i == 'PASSWORD'){
                var md5 = crypto.createHash('md5');
                md5.update(param[i],'utf-8');
                var val = md5.digest('hex');
                param[i] = val.toUpperCase();
            }
			if(i != 'ID' && i!="LOGIN_STATUS"&&i!="REGISTRY_TIME"&&i!="OVER_LOGIN_TIME"&&i!="DATA_BIRTH"){
				setNameArr.push(i+'='+"'"+param[i]+"'");
			}
		}
		return 'UPDATE user_info SET ' + setNameArr.join(',') + ' WHERE ID='+param.ID;
    },
	redisData:[
		{sql:'SELECT * FROM building_type',name:'building'}, //房产类型列表
	    {sql:'SELECT * FROM credit_ask',name:'credit'}, //征信要求列表
	    {sql:'SELECT * FROM lending_ask',name:'lendingAsk'}, //放款要求列表
	    {sql:'SELECT * FROM lending_type',name:'lendingType'}, //放款方式列表
	    {sql:'SELECT * FROM loan_type',name:'loan'}, //贷款用途类型列表
	    {sql:'SELECT * FROM note_affair',name:'noteAffair'}, //可沟通范围/注意事项列表
	    {sql:'SELECT * FROM trade_type',name:'trade'}, //行业列表
	    {sql:'SELECT * FROM repayment' , name:'repayment'}, //还款方式
	    {sql:'SELECT * FROM default_interest',name:'interest'}, //提前罚息列表
		{sql:'SELECT * FROM loan_charge',name:'loanCharge'}, //放款手续列表
		{sql:'SELECT * FROM datum',name:'datum'}, //证件资料列表
		{sql:'SELECT * FROM debt_ask',name:'debtAsk'}, //负债要求列表
		{sql:'SELECT * FROM safe_work',name:'safeWork'}, //保险公司
		{sql:'SELECT * FROM social_ask',name:'socialAsk'}, //社保要求
		{sql:'SELECT * FROM user_role',name:'userRole'} //角色
	]
};